﻿using System;
using System.Collections.Generic;
using System.Text;
using NHibernate;
using System.Data;
using BEGDC.Utility.NHibernate;
using Castle.Services.Transaction;
using System.Collections;
using System.Xml;
using Castle.Facilities.NHibernateIntegration;

namespace BEGDC.PreContract.BLL
{
    public partial class BD : IBD
    {


        public BEGDC.PreContract.DAL.BD GetBDByName(string bdCode)
        {
            BEGDC.PreContract.DAL.BD bd = BEGDC.PreContract.DAL.BD.FindFirst(new NHibernate.Expression.EqExpression(BEGDC.PreContract.DAL.BD.CONST_BDCode, bdCode));

            if (bd == null)
                bd = BEGDC.PreContract.DAL.BD.FindFirst(new NHibernate.Expression.EqExpression(BEGDC.PreContract.DAL.BD.CONST_BDDescription, bdCode));


            return bd;

        }

        //Joan.lu 2008-09-19
        //Get Active BD dimention by user permission
        public DataTable GetBDByPermission(long employeeID, bool isAdmin)
        {
            string strSql;
            using (ISession session = factory.OpenSession())
            {
                if (isAdmin)
                    strSql = "SELECT a.BGID,b.BGCode,a.BDID,a.BDCode FROM dbo.BD a INNER JOIN dbo.BG b ON a.BGID=b.BGID WHERE a.Active=1 ORDER BY b.SortNumber,a.SortNumber";
                else
                    strSql = "SELECT a.BGID,c.BGCode,a.BDID,a.BDCode FROM dbo.BD a INNER JOIN dbo.BDAssignment b ON a.BdID=b.BDID INNER JOIN dbo.BG c ON a.BGID=c.BGID WHERE a.Active=1 AND b.EmployeeID=" + employeeID.ToString() + " ORDER BY a.SortNumber";

                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch;
            }
        }
        public DataTable GetBDByPermission(long employeeID, bool isAdmin, int year)
        {
            string strSql;
            using (ISession session = factory.OpenSession())
            {
                if (year > 0)
                    strSql = "SELECT a.BGID,b.BGCode,a.BDID,a.BDCode FROM dbo.BD a INNER JOIN dbo.BG b ON a.BGID=b.BGID where BDID in (SELECT BDID FROM PreContractInfo WHERE year(SignActual)=" + year + " AND StatusID=12)  ORDER BY b.SortNumber,a.SortNumber";

                else if (isAdmin)
                    strSql = "SELECT a.BGID,b.BGCode,a.BDID,a.BDCode FROM dbo.BD a INNER JOIN dbo.BG b ON a.BGID=b.BGID WHERE a.Active=1 ORDER BY b.SortNumber,a.SortNumber";
                else
                    strSql = "SELECT a.BGID,c.BGCode,a.BDID,a.BDCode FROM dbo.BD a INNER JOIN dbo.BDAssignment b ON a.BdID=b.BDID INNER JOIN dbo.BG c ON a.BGID=c.BGID WHERE a.Active=1 AND b.EmployeeID=" + employeeID.ToString() + " ORDER BY a.SortNumber";

                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch;
            }
        }

        //Edwin.fan 2008-9-25
        //Get the province with the max ProvinceId
        public DataTable GetMaxBD()
        {
            using (ISession session = factory.OpenSession())
            {
                string strSql = "SELECT max(BDID) FROM BD";

                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch;
            }

        }

        //Edwin.fan 2008-9-26
        //Get the BD and change BGID to BGCODE
        public DataTable GetBD_BGCode_Table()
        {
            using (ISession session = factory.OpenSession())
            {
                string strSql = "SELECT a.*,b.BGCode FROM BD as a inner join BG as b on a.BGID = b.BGID ORDER BY a.SortNumber ASC";

                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch;
            }

        }
    }
}



